﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;

namespace matrixdotnet.DBHelperLib
{
    public class DBHelper
    {
        private DbConnection cn;
        private DbCommand com;
        private DbProviderFactory factory;

        public DBHelper(string conStrName)
        {
            string conStr = ConfigurationManager.ConnectionStrings[conStrName].ConnectionString;
            string providerName = ConfigurationManager.ConnectionStrings[conStrName].ProviderName;

            factory = DbProviderFactories.GetFactory(providerName);
            cn = factory.CreateConnection();
            cn.ConnectionString = conStr;
            com = factory.CreateCommand();
            com.Connection = cn;
        }

        public DbCommand Command
        {
            get { return com; }
        }

        public void Open()
        {
            if (cn.State == ConnectionState.Open)
                return;

            cn.Open();
        }

        public void BeginTransaction()
        {
            Open();
            com.Transaction = cn.BeginTransaction();
        }

        public void Commit()
        {
            try
            {
                com.Transaction.Commit();
            }
            finally
            {
                com.Transaction = null;
            }
        }

        public void Rollback()
        {
            try
            {
                com.Transaction.Rollback();
            }
            finally
            {
                com.Transaction = null;
            }
        }

        public DbDataReader ExecuteSPReader(string spName, params DbParameter[] parameters)
        {
            com.Parameters.AddRange(parameters);
            return ExecuteSPReader(spName);
        }

        public DbDataReader ExecuteSPReader(string spName)
        {
            com.CommandType = CommandType.StoredProcedure;
            return ExecuteCommandText<DbDataReader>(spName, com.ExecuteReader);
        }

        public int ExecuteSPNonQuery(string spName, params DbParameter[] parameters)
        {
            com.Parameters.AddRange(parameters);
            return ExecuteSPNonQuery(spName);
        }

        public int ExecuteSPNonQuery(string spName)
        {
            com.CommandType = CommandType.StoredProcedure;
            return ExecuteCommandText<int>(spName, com.ExecuteNonQuery);
        }

        public object ExecuteSPScalar(string spName, params DbParameter[] parameters)
        {
            com.Parameters.AddRange(parameters);
            return ExecuteSPScalar(spName);
        }

        public object ExecuteSPScalar(string spName)
        {
            com.CommandType = CommandType.StoredProcedure;
            return ExecuteCommandText<object>(spName, com.ExecuteScalar);
        }

        public DbDataReader ExecuteReader(string sql, params DbParameter[] parameters)
        {
            com.Parameters.AddRange(parameters);
            return ExecuteReader(sql);
        }

        public DbDataReader ExecuteReader(string sql)
        {
            com.CommandType = CommandType.Text;
            return ExecuteCommandText<DbDataReader>(sql, com.ExecuteReader);
        }

        public object ExecuteScalar(string sql, params DbParameter[] parameters)
        {
            com.Parameters.AddRange(parameters);
            return ExecuteScalar(sql);
        }

        public object ExecuteScalar(string sql)
        {
            com.CommandType = CommandType.Text;
            return ExecuteCommandText<object>(sql, com.ExecuteScalar);
        }

        public int ExecuteNonQuery(string sql, params DbParameter[] parameters)
        {
            com.Parameters.AddRange(parameters);
            return ExecuteNonQuery(sql);
        }

        public int ExecuteNonQuery(string sql)
        {
            com.CommandType = CommandType.Text;
            return ExecuteCommandText<int>(sql, com.ExecuteNonQuery);
        }

        public DbParameter CreateParameter()
        {
            return factory.CreateParameter();
        }

        public DbParameter CreateParameter(string name, object value)
        {
            DbParameter p = factory.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            return p;
        }

        public void Close()
        {
            cn.Close();
        }

        private TResult ExecuteCommandText<TResult>(string commandText, Func<TResult> commandMethod)
        {
            com.CommandText = commandText;
            Open();

            if (com.Parameters.Count == 0)
                return commandMethod();

            try
            {
                return commandMethod();
            }
            finally
            {
                com.Parameters.Clear();
            }
        }
    }
    
}
